package com.zp.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class DbHelper {

    public Connection conn = null;
    public PreparedStatement psmt = null;
    public ResultSet rs = null;

    public JdbcUtil jdbcUtil = new JdbcUtil();

    public DbHelper() {

    }

    /**
     * 查询语句
     * @param sql 查询SQL语句
     */
    public ResultSet executeQuery(String sql) {
        conn = null;
        psmt = null;
        rs = null;
        try{
            conn = jdbcUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            jdbcUtil.close(rs, psmt, conn);
        }
        return rs;
    }

    /**
     * 查询语句
     * @param sql 查询SQL
     * @param params 参数集合
     */
    public ResultSet executeQuery(String sql, List<Object> params) {
        conn = null;
        psmt = null;
        rs = null;
        try{
            conn = jdbcUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            setParams(psmt, params);
            rs = psmt.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
            jdbcUtil.close(rs, psmt, conn);
        }
        return rs;
    }


    /**
     * 执行操作
     * @param sql 需要执行的SQL
     */
    public int executeOperate(String sql) {
        int count = 0;
        Connection conn = null;
        PreparedStatement psmt = null;

        try {
            conn = jdbcUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            count = psmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(psmt, conn);
        }
        return count;
    }

    /**
     * 执行操作
     * @param sql 需要执行的SQL
     * @param params 参数列表
     */
    public int executeOperate(String sql, List<Object> params) {
        int count = 0;
        Connection conn = null;
        PreparedStatement psmt = null;

        try {
            conn = jdbcUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            setParams(psmt, params);
            count = psmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(psmt, conn);
        }
        return count;
    }

    /**
     * 执行返回单个值
     * @param sql
     * @return 单个值
     */
    public Object executeScalar(String sql) {
        Object resObj = null;
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            rs = psmt.executeQuery();
            if(rs.next()) {
                resObj = rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, psmt, conn);
        }
        return resObj;
    }

    /**
     * 执行返回单个值
     * @param sql
     * @param params 参数列表
     * @return 单个值
     */
    public Object executeScalar(String sql, List<Object> params) {
        Object resObj = null;
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;

        try {
            conn = jdbcUtil.getConnection();
            psmt = conn.prepareStatement(sql);
            setParams(psmt, params);
            rs = psmt.executeQuery();
            if(rs.next()) {
                resObj = rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtil.close(rs, psmt, conn);
        }
        return resObj;
    }

    /**
     *
     * @param params 参数集合
     */
    public void setParams(PreparedStatement psmt, List<Object> params) throws SQLException {
        if (params != null && params.size() >0) {
            for(int i = 0;i < params.size();i++) {
                psmt.setObject(i +  1, params.get(i));
            }
        }
    }


}
